package com.lswms.modules.lswms.dao.orders;

import java.util.List;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.lswms.common.dao.BaseDao;
import com.lswms.modules.lswms.entity.orders.OrderdetaillistEntity;

/**
 * 出库明细记录
 *
 * @author DX sunlightcs@gmail.com
 * @since 1.0.0 2021-06-24
 */
@Mapper
public interface OrderdetaillistDao extends BaseDao<OrderdetaillistEntity> {

	@Select(value ="select isnull( max(linelist),0)+1 from lswms_orderdetaillist where orderkey=#{orderkey} and line=#{line} ")
	int getNextOrderdetaillistLinelist(@Param("orderkey") String orderkey, @Param("line")Integer line);


	@Select(value="select lswms_orderdetaillist.*,  a.listname  statusstr, lswms_sku.descr , lswms_sku.factorycdoe factorycdoe,b.listname  fromboxstr,c.listname boxstr,d.listname  toboxstr "
			+ "	from lswms_orderdetaillist left join lswms_codelist a  on lswms_orderdetaillist.status = a.list and a.code='OrderlistStatus'  "
			+ "		left join lswms_sku on lswms_orderdetaillist.sku = lswms_sku.sku  "
			+ "		left join lswms_codelist b on lswms_orderdetaillist.frombox = b.list and b.code='BOXLIST'  "
			+ "		left join lswms_codelist c on lswms_orderdetaillist.box = c.list and c.code='BOXLIST'  "
			+ "		left join lswms_codelist d on lswms_orderdetaillist.tobox = d.list and d.code='BOXLIST'  ${ew.customSqlSegment} ")
	IPage<OrderdetaillistEntity> selectPageBySql(IPage<OrderdetaillistEntity> page,
			@Param(Constants.WRAPPER)QueryWrapper<OrderdetaillistEntity> wrapper);
	
	

	@Select(value=" select lswms_orderdetaillist.*,  a.listname  statusstr, lswms_sku.descr , lswms_sku.factorycdoe factorycdoe, lswms_sku.isuniquecode isuniquecode,b.listname  fromboxstr,c.listname boxstr,d.listname  toboxstr ,isnull(e.qty,0) inventoryqty "
			+ " from lswms_orderdetaillist left join lswms_codelist a  on lswms_orderdetaillist.status = a.list and a.code='OrderlistStatus'   "
			+ "			left join lswms_sku on lswms_orderdetaillist.sku = lswms_sku.sku   "
			+ " left join lswms_codelist b on lswms_orderdetaillist.frombox = b.list and b.code='BOXLIST'   "
			+ " left join lswms_codelist c on lswms_orderdetaillist.box = c.list and c.code='BOXLIST'   "
			+ " left join lswms_codelist d on lswms_orderdetaillist.tobox = d.list and d.code='BOXLIST'  "
			+ " left join lswms_inventory e  on  lswms_orderdetaillist.lot = e.lot and  lswms_orderdetaillist.loc = e.location "
			+ " and  lswms_orderdetaillist.tray = e.tray and  lswms_orderdetaillist.box  = e.box  ${ew.customSqlSegment} ")
	List<OrderdetaillistEntity> selectListBySql(@Param(Constants.WRAPPER)QueryWrapper<OrderdetaillistEntity> queryWrapper);

	@Select(value="select odl.id,odl.orderkey	,odl.line	,odl.linelist	,odl.sku,odl.unit,odl.pack,odl.status,odl.qty,odl.actqty,odl.fromloc,odl.fromtray,odl.frombox,odl.loc,odl.tray,odl.box,odl.toloc,odl.totray,odl.tobox,odl.lot\n"
			+ ",odl.remark,odl.backstatus,odl.backdate,odl.backresult,odl.backcode,odl.warehouse,odl.pickuser,odl.picktime,odl.checkuser,odl.checktime,odl.loader,odl.loadtime,odl.adduser,odl.addtime,odl.updatuser,odl.updatetime, \n"
			+ "a.listname  statusstr, lswms_sku.descr , lswms_sku.factorycdoe factorycdoe,b.listname  fromboxstr,c.listname boxstr,d.listname  toboxstr ,\n"
			+ "lot.lot1,lot.lot2,lot.lot3,lot.lot4,lot.lot5,lot.lot6,lot.lot7,lot.lot8,lot.lot9,lot.lot10,lot.lot11,lot.lot12,lot.lot13,lot.lot14,lot.lot15,e.listname typestr, od.cardId,od.recipient,od.recipientId\n"
			+ " from lswms_orderdetaillist odl\n"
			+ "	left join lswms_codelist a  on odl.status = a.list and a.code='OrderlistStatus'  \n"
			+ "	left join lswms_sku on odl.sku = lswms_sku.sku  \n"
			+ "	left join lswms_codelist b on odl.frombox = b.list and b.code='BOXLIST'  \n"
			+ "	left join lswms_codelist c on odl.box = c.list and c.code='BOXLIST'  \n"
			+ "    left join lswms_codelist d on odl.tobox = d.list and d.code='BOXLIST' \n"
			+ "	join lswms_orders od on odl.orderkey  = od.orderkey \n"
			+ "	left join lswms_codelist e on od.type = e.list and e.code='orderType'\n"
			+ "	join lswms_lot lot on odl.lot  = lot.lot   ${ew.customSqlSegment} ")
	IPage<OrderdetaillistEntity> selectPageListBySql(IPage<OrderdetaillistEntity> page,
			@Param(Constants.WRAPPER)QueryWrapper<OrderdetaillistEntity> wrapper);


	@Select(value="select odl.id,odl.orderkey	,odl.line	,odl.linelist	,odl.sku,odl.unit,odl.pack,odl.status,odl.qty,odl.actqty,odl.fromloc,odl.fromtray,odl.frombox,odl.loc,odl.tray,odl.box,odl.toloc,odl.totray,odl.tobox,odl.lot\n"
			+ ",odl.remark,odl.backstatus,odl.backdate,odl.backresult,odl.backcode,odl.warehouse,odl.pickuser,odl.picktime,odl.checkuser,odl.checktime,odl.loader,odl.loadtime,odl.adduser,odl.addtime,odl.updatuser,odl.updatetime, \n"
			+ "a.listname  statusstr, lswms_sku.descr , lswms_sku.factorycdoe factorycdoe,b.listname  fromboxstr,c.listname boxstr,d.listname  toboxstr ,\n"
			+ "lot.lot1,lot.lot2,lot.lot3,lot.lot4,lot.lot5,lot.lot6,lot.lot7,lot.lot8,lot.lot9,lot.lot10,lot.lot11,lot.lot12,lot.lot13,lot.lot14,lot.lot15,e.listname typestr, od.cardId,od.recipient,od.recipientId\n"
			+ " from lswms_orderdetaillist odl\n"
			+ "	left join lswms_codelist a  on odl.status = a.list and a.code='OrderlistStatus'  \n"
			+ "	left join lswms_sku on odl.sku = lswms_sku.sku  \n"
			+ "	left join lswms_codelist b on odl.frombox = b.list and b.code='BOXLIST'  \n"
			+ "	left join lswms_codelist c on odl.box = c.list and c.code='BOXLIST'  \n"
			+ "    left join lswms_codelist d on odl.tobox = d.list and d.code='BOXLIST' \n"
			+ "	join lswms_orders od on odl.orderkey  = od.orderkey \n"
			+ "	left join lswms_codelist e on od.type = e.list and e.code='orderType'\n"
			+ "	join lswms_lot lot on odl.lot  = lot.lot   ${ew.customSqlSegment} ")
	List<OrderdetaillistEntity> listBySql(@Param(Constants.WRAPPER)QueryWrapper<OrderdetaillistEntity> wrapper);





}
